﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.Common;
using System.Xml;
using IDAdmin.Lib.Utils;
using System.Web;
using System.Web.Script.Serialization;
using System.ComponentModel;
using MySql;
using MySql.Data.MySqlClient;
using System.Globalization;
using Cassandra;
using System.Linq;

namespace IDAdmin.Lib.DataLayer
{
    public class WebDB
    {

        ///////////////////////////////////////////////////////////////////////////

        #region Authentication

        /// <summary>
        /// Kiểm tra thông tin đăng nhập của User (user phải có level từ 50 trở lên và đã kích hoạt email).
        /// Hàm trả về DataRow chứa thông tin ID, Level của User nếu hợp lệ, ngược lại trả về null
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="Password"></param>
        /// <param name="Ip"></param>
        /// <returns></returns>
        public static DataRow User_Validate(string UserName, string Password, string Ip)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Login", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Password", SqlDbType.NVarChar, Password));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@IP", SqlDbType.NVarChar, Ip));

                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy mã game được chỉ định đối với User
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static string User_GetGameID(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_GetGameID", connection);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    return IDAdmin.Lib.Utils.Converter.ToString(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Quản lý tài khoản

        /// <summary>
        /// Tìm kiếm và lấy danh sách tài khoản của user.
        /// (FindValue="": Bỏ qua tham số. Status = -100: bỏ qua tham số. Level =-1 : Bỏ qua tham số)
        /// </summary>
        /// <param name="FindValue">CustomerID hoặc UserName cần tìm (rỗng nếu bỏ qua)</param>
        /// <param name="Status">Trạng thái (-1: khóa, 0: chưa kích hoạt, 1: đã kích hoạt, giá trị khác = toàn bộ)</param>
        /// <param name="Level">level quy định loại user (truyền giá trị âm nếu bỏ qua tham số này khi tìm kiếm)</param>
        /// <param name="StartPos">Vị trí</param>
        /// <param name="PageSize">Số lượng hiển thị trên 1 trang</param>
        /// <returns></returns>
        public static DataTable User_Select(string FindValue, int Status, int Level, int StartPos, int PageSize)
        {
            try
            {
                //if (FindValue != "")
                //{
                //    FindValue = "%" + FindValue + "%";
                //}

                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_Select", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FindValue", SqlDbType.NVarChar, FindValue));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Status", SqlDbType.Int, Status));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Level", SqlDbType.Int, Level));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartPos", SqlDbType.Int, StartPos));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PageSize", SqlDbType.Int, PageSize));
                    return DbHelper.FillDataTable(cmd, Meta.USER);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable Gosu_User_Select(string FindValue, int StartPos, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GosuAccountList", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Username", SqlDbType.NVarChar, FindValue));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartPos", SqlDbType.Int, StartPos));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PageSize", SqlDbType.Int, PageSize));
                    return DbHelper.FillDataTable(cmd, Meta.USER);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable Gosu_Summary(DateTime from, DateTime to)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_Gosu", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateFrom", SqlDbType.DateTime, from));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateTo", SqlDbType.DateTime, to));
                    return DbHelper.FillDataTable(cmd, Meta.USER);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static long Gosu_Total()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_TotalGosu", connection);
                    return Converter.ToLong(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy DataRow chứa thông tin chi tiết của 1 user
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="CustomerID"></param>
        /// <returns></returns>
        public static DataRow User_Details(string CustomerID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_Details", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@CustomerID", SqlDbType.NVarChar, CustomerID));
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Điểm tích lũy của user
        /// </summary>
        /// <returns></returns>
        public static int User_AccumulationPoint(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("sp_AccPoint_Get", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    return Utils.Converter.ToInt(cmd.ExecuteScalar());
                }
            }
            catch
            {
                return 0;
            }
        }

        /// <summary>
        /// Thay đổi mật khẩu của user
        /// </summary>
        /// <param name="LoginName">Tên user đang đăng nhập</param>
        /// <param name="CustomerID">ID của user cần đổi pass</param>
        /// <param name="Password">Password</param>
        /// <returns></returns>
        public static void User_ChangePassword(string LoginName, string CustomerID, string Password)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_ChangePassword", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@CustomerID", SqlDbType.NVarChar, CustomerID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Password", SqlDbType.NVarChar, Password));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật thông tin của User
        /// </summary>
        /// <param name="LoginName"></param>
        /// <param name="ID"></param>
        /// <param name="CustomerID"></param>
        /// <param name="Email"></param>
        /// <param name="NumberID"></param>
        /// <param name="Name"></param>
        /// <param name="Level"></param>
        /// <param name="PhoneNumber"></param>
        /// <param name="Address"></param>
        /// <param name="Birthday"></param>
        /// <param name="Gender"></param>
        /// <param name="LocationID"></param>
        /// <param name="Status"></param>
        /// <param name="QuestionID"></param>
        /// <param name="Answer"></param>
        public static void User_ChangeInfo(string LoginName, long ID, string CustomerID, string Email,
                                    string NumberID, string Name, int Level, string PhoneNumber, string Address,
                                    DateTime Birthday, bool Gender, int LocationID, int Status,
                                    int QuestionID, string Answer)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_ChangeInfo", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ID", SqlDbType.BigInt, ID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@CustomerID", SqlDbType.NVarChar, CustomerID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Email", SqlDbType.NVarChar, Email));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@NumberID", SqlDbType.NVarChar, NumberID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Name", SqlDbType.NVarChar, Name));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Level", SqlDbType.Int, Level));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PhoneNumber", SqlDbType.NVarChar, PhoneNumber));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Address", SqlDbType.NVarChar, Address));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Birthday", SqlDbType.DateTime, Birthday));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Gender", SqlDbType.Bit, Gender));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LocationID", SqlDbType.Int, LocationID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Status", SqlDbType.Int, Status));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@QuestionID", SqlDbType.Int, QuestionID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Answer", SqlDbType.NVarChar, Answer));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Kiểm tra tài khoản tồn tại hay không
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static bool User_Exists(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_User_CheckExists", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    return Utils.Converter.ToBoolean(DbHelper.ExecuteScalar(cmd));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable ChanellingUser_Select(string Community, string UserName, int Page, int PageSize)
        {
            {
                try
                {
                    using (SqlConnection connection = ConnectionHelper.GetConnection())
                    {
                        SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingUser_Select", connection);
                        cmd.Parameters.AddWithValue("@Community", Community);
                        cmd.Parameters.AddWithValue("@UserName", UserName);
                        cmd.Parameters.AddWithValue("@Page", Page);
                        cmd.Parameters.AddWithValue("@PageSize", PageSize);

                        return DbHelper.FillDataTable(cmd, "ChanellingUser");
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Bài viết

        /// <summary>
        /// Lấy danh sách 10 bài viết tính từ bài viết có ID là StartID 
        /// (StartID = 0: Lấy từ bài viết sau cùng nhất)
        /// </summary>
        /// <param name="StartID"></param>
        /// <returns></returns>
        public static DataTable News_Select(long StartID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Select", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartID", SqlDbType.BigInt, StartID));
                    return DbHelper.FillDataTable(cmd, Meta.NEWS);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy thông tin chi tiết 1 bài viết
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataRow News_Details(long ID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Details", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ID", SqlDbType.BigInt, ID));
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Bổ sung bài viết
        /// </summary>
        /// <param name="Title"></param>
        /// <param name="Content"></param>
        /// <param name="Category"></param>
        /// <param name="Writter"></param>
        public static void News_Insert(string Title, string Content, int Category, string Writter, string GameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Insert", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Title", SqlDbType.NVarChar, Title));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Content", SqlDbType.NText, Content));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Category", SqlDbType.Int, Category));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Writter", SqlDbType.NVarChar, Writter));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, GameID));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật bài viết
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="Title"></param>
        /// <param name="Content"></param>
        /// <param name="Category"></param>
        /// <param name="Writter"></param>
        public static void News_Update(long ID, string Title, string Content, int Category, string Writter, string GameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Update", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ID", SqlDbType.BigInt, ID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Title", SqlDbType.NVarChar, Title));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Content", SqlDbType.NText, Content));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Category", SqlDbType.Int, Category));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Writter", SqlDbType.NVarChar, Writter));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, GameID));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Xóa bài viết
        /// </summary>
        /// <param name="ID"></param>
        public static void News_Delete(long ID, string LoginName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_News_Delete", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ID", SqlDbType.BigInt, ID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Giao dịch

        /// <summary>
        /// Tra cứu giao dịch nạp vàng vào game
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="Type"></param>
        /// <param name="Server"></param>
        /// <param name="PinNumber"></param>
        /// <param name="Account"></param>
        /// <param name="StartPos"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable CardLog_Select(DateTime? FromDate, DateTime? ToDate, string Type, string Server, string PinNumber, string Account, string PayPurpose, long StartPos, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_CardLog_Select", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FromDate", SqlDbType.DateTime, FromDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ToDate", SqlDbType.DateTime, ToDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Type", SqlDbType.NVarChar, Type));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, AppManager.GameID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ServerName", SqlDbType.NVarChar, Server));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PinNumber", SqlDbType.NVarChar, PinNumber));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Account", SqlDbType.NVarChar, Account));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PayPurpose", SqlDbType.NVarChar, PayPurpose));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartPos", SqlDbType.BigInt, StartPos));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@PageSize", SqlDbType.Int, PageSize));
                    return DbHelper.FillDataTable(cmd, Meta.CARDLOG);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Chi tiết giao dịch
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static DataRow CardLog_Details(long ID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_CardLog_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", ID);

                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lịch sử quá trình xử lý giao dịch
        /// </summary>
        /// <param name="ExchangeID"></param>
        /// <returns></returns>
        public static DataTable PaymentGateLog_Select(string ExchangeID)
        {
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                SqlCommand cmd = new SqlCommand("spAdmin_PaymentGateLog_Select", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ExchangeID", ExchangeID);

                return DbHelper.FillDataTable(cmd, "PaymentGateLog");
            }
        }

        /// <summary>
        /// Tra log giao dịch theo lỗi
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="Type"></param>
        /// <param name="ErrorCode"></param>
        /// <returns></returns>
        public static DataTable CardLog_SelectError(DateTime? FromDate, DateTime? ToDate, string Type, int Status, int ErrorCode)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_CardLog_GetErrorLog", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, AppManager.GameID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FromDate", SqlDbType.DateTime, FromDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ToDate", SqlDbType.DateTime, ToDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Type", SqlDbType.NVarChar, Type));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Status", SqlDbType.Int, Status));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ErrorCode", SqlDbType.Int, ErrorCode));

                    return DbHelper.FillDataTable(cmd, Meta.CARDLOG);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// cập nhật lại cardlog
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="ServerName"></param>
        /// <param name="Amount"></param>
        /// <param name="Status"></param>
        /// <param name="ErrorCode"></param>
        public static void CardLog_Update(long ID, string ServerName, int Amount, int Status, int ErrorCode)
        {
            try
            {
                using (SqlConnection connnection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_CardLog_Update", connnection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ID", ID);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@ServerName", ServerName);
                    cmd.Parameters.AddWithValue("@Amount", Amount);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    cmd.Parameters.AddWithValue("@ErrorCode", ErrorCode);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Tra cứu lịch sử nạp tiền vào ví GOSU
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="Type"></param>
        /// <param name="PinNumber"></param>
        /// <param name="Account"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable WalletRechargeLog_Select(DateTime? FromDate, DateTime? ToDate, string Type, string PinNumber, string Account, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_WalletRechargeLog_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);
                    cmd.Parameters.AddWithValue("@Type", Type);
                    cmd.Parameters.AddWithValue("@PinNumber", PinNumber);
                    cmd.Parameters.AddWithValue("@Account", Account);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "WalletRechargeLog");

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Tra cứu lịch sử mua CODE bằng GOSU
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="Kind"></param>
        /// <param name="Amount"></param>
        /// <param name="Account"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable WalletSpendingLog_Select(DateTime? FromDate, DateTime? ToDate, string Kind, int Amount, string Account, int Status, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_WalletSpendingLog_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@CodeType", Kind);
                    cmd.Parameters.AddWithValue("@Amount", Amount);
                    cmd.Parameters.AddWithValue("@Account", Account);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    return DbHelper.FillDataTable(cmd, "WalletSpendingLog");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật log giao dịch. Hàm trả về:
        /// -99: Lỗi hệ thống
        ///  -1: Giao dịch không tồn tại hoặc không cho phép cập nhật  
        ///   0: Cập nhật không thành công
        ///   1: Cập nhật thành công
        /// </summary>
        /// <param name="RechargeID"></param>
        /// <param name="Amount"></param>
        /// <param name="Status"></param>
        /// <param name="ErrorCode"></param>
        /// <param name="Msg"></param>
        /// <param name="LoginName"></param>
        /// <param name="IP"></param>
        /// <returns></returns>
        public static int WalletRechargeLog_Update(long RechargeID, int Amount, int Status, int ErrorCode, string Msg, string LoginName, string LogAction, string IP)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_WalletRechargeLog_Update", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RechargeID", RechargeID);
                    cmd.Parameters.AddWithValue("@Amount", Amount);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    cmd.Parameters.AddWithValue("@ErrorCode", ErrorCode);
                    cmd.Parameters.AddWithValue("@Msg", Msg);
                    cmd.Parameters.AddWithValue("@LoginName", LoginName);
                    cmd.Parameters.AddWithValue("@LogAction", LogAction);
                    cmd.Parameters.AddWithValue("@IP", IP);

                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                    pResult.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(pResult);

                    DbHelper.ExecuteNonQuery(cmd);
                    return Lib.Utils.Converter.ToInt(pResult.Value, -99);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Chi tiết giao dịch nạp tiền vào ví GOSU
        /// </summary>
        /// <param name="RechargeID"></param>
        /// <returns></returns>
        public static DataRow WalletRechargeLog_Details(long RechargeID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_WalletRechargeLog_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@RechargeID", RechargeID);
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Tra cứu log giao dịch thanh toán qua dịch vụ thanh toán OAuth
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <param name="ServiceID"></param>
        /// <param name="SearchValue"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable OAuthPaymentLog_Select(DateTime? FromDate, DateTime? ToDate, string ServiceID, string SearchValue, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentLog_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@SearchValue", SearchValue);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);
                    return DbHelper.FillDataTable(cmd, "OAuthPaymentLog");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Chi tiết giao dịch
        /// </summary>
        /// <param name="LogID"></param>
        /// <returns></returns>
        public static DataRow OAuthPaymentLog_Details(long LogID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentLog_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@LogID", LogID);
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Hoạt động - Sự kiện

        /// <summary>
        /// Lấy danh sách các user, điểm hảo hữu và số lượng giftcode đã cấp phát cho user
        /// </summary>
        /// <param name="GameID"></param>
        /// <param name="FindValue"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable UserFriend_Select(string GameID, string FindValue, int Page, int PageSize)
        {
            try
            {
                FindValue = FindValue.Trim();
                if (FindValue != "")
                {
                    FindValue = "%" + FindValue + "%";
                }
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_UserFriend_Select", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.AddWithValue("@FindValue", FindValue);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "UserFriend");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cấp GiftCode cho User
        /// </summary>
        /// <param name="GameID"></param>
        /// <param name="ServerName"></param>
        /// <param name="UserName"></param>
        /// <param name="ReceivedType"></param>
        /// <returns></returns>
        public static bool GiveGiftCode(string GameID, string ServerName, string UserName, int ReceivedType)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_GiftCode_Set", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.AddWithValue("@ServerName", ServerName);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@ReceivedType", ReceivedType);

                    SqlParameter paraResult = new SqlParameter("@Result", SqlDbType.Int);
                    paraResult.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(paraResult);

                    cmd.ExecuteNonQuery();

                    return IDAdmin.Lib.Utils.Converter.ToInt(paraResult.Value) == 1;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Đếm số GiftCode chưa cấp
        /// </summary>
        /// <param name="GameID"></param>
        /// <returns></returns>
        public static long GiftCode_CountFree()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GiftCode_CountFree", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);

                    return Lib.Utils.Converter.ToLong(cmd.ExecuteScalar());
                }
            }
            catch
            {
                return -1;
            }
        }

        #endregion

        ///////////////////////////////////////////////////////////////////////////

        #region Thống kê

        /// <summary>
        /// Thống kê số lượng User
        /// </summary>
        /// <returns></returns>
        public static long Statistics_UserAccount(string GameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_CountOfUser", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    return Utils.Converter.ToLong(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static long Statistics_ActivatedUser(string GameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_CountOfActivatedUser", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    return Utils.Converter.ToLong(cmd.ExecuteScalar());
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê kích hoạt dựa trên Kind: Free, Trial, VIP, NORMAL...
        /// </summary>
        /// <param name="GameID"></param>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="kind"></param>
        /// <returns></returns>
        public static DataTable Statistics_ActivatedUser(string GameID, DateTime StartDate, DateTime EndDate, string kind)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_ActivatedUserByDate", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateFrom", SqlDbType.DateTime, StartDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateTo", SqlDbType.DateTime, EndDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Kind", SqlDbType.VarChar, kind));
                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê CDKey sử dụng theo ngày (Sử dụng cho report tới SG và CUBI)
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="kind"></param>
        /// <returns></returns>
        public static DataTable Statistics_CDKey(DateTime StartDate, DateTime EndDate, string kind)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_CACK_CDKey_Select", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FromDate", SqlDbType.DateTime, StartDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ToDate", SqlDbType.DateTime, EndDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Kind", SqlDbType.VarChar, kind));
                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê số lượng User đăng ký trong ngày
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns></returns>
        public static DataTable Statistics_UserAccount(string GameID, DateTime StartDate, DateTime EndDate)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistics_RegUserByDate", connection);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateFrom", SqlDbType.DateTime, StartDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@DateTo", SqlDbType.DateTime, EndDate));
                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê doanh thu hàng ngày
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="TimeLine"></param>
        /// <param name="Server"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        public static DataTable Statistics_SumaryByDate(DateTime StartDate, DateTime EndDate, int TimeLine, string Server, string Type)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Statistic_SumaryByDate", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@StartDate", SqlDbType.DateTime, StartDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@EndDate", SqlDbType.DateTime, EndDate));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@TimeLine", SqlDbType.Int, TimeLine));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, AppManager.GameID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@ServerName", SqlDbType.NVarChar, Server));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Type", SqlDbType.NVarChar, Type));
                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê top nộp tiền
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="Server"></param>
        /// <param name="Account"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable Statistic_TopRecharge(DateTime? StartDate, DateTime? EndDate, string Server, string Account, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Statistics_SumCardLogByUser", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", StartDate);
                    cmd.Parameters.AddWithValue("@ToDate", EndDate);
                    cmd.Parameters.AddWithValue("@Account", Account);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@Server", Server);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê giao dịch của khách hàng phía đối tác Chanelling
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <param name="Server"></param>
        /// <param name="Community"></param>
        /// <param name="Account"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable Statistic_TopChanellingRecharge(DateTime? StartDate, DateTime? EndDate, string Server, string Community, string Account, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Statistics_SumCardLogByChanellingUser", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", StartDate);
                    cmd.Parameters.AddWithValue("@ToDate", EndDate);
                    cmd.Parameters.AddWithValue("@Account", Account);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@Server", Server);
                    cmd.Parameters.AddWithValue("@Community", Community);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thống kê tổng hợp tiền nạp vào game theo từng game và từng hình thức thanh toán
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public static DataTable Statistic_SumaryByGameAndType(DateTime? FromDate, DateTime? ToDate)
        {
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                SqlCommand cmd = new SqlCommand("spAdmin_Statistic_SumaryByGameAndType", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FromDate", FromDate);
                cmd.Parameters.AddWithValue("@ToDate", ToDate);
                return DbHelper.FillDataTable(cmd, "Statistic");
            }
        }

        /// <summary>
        /// Thống kê tổng hợp giao dịch nạp tiền vào ví GOSU
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public static DataTable Statistic_SumaryWalletRecharge(DateTime FromDate, DateTime ToDate)
        {
            using (SqlConnection connection = ConnectionHelper.GetConnection())
            {
                SqlCommand cmd = new SqlCommand("spAdmin_Statistic_SumaryWalletRecharge", connection);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@FromDate", FromDate);
                cmd.Parameters.AddWithValue("@ToDate", ToDate);
                return DbHelper.FillDataTable(cmd, "Statistic");
            }
        }

        /// <summary>
        /// Thống kê nạp tiền theo cộng đồng
        /// </summary>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public static DataTable Statistic_SumaryByCommunity(DateTime FromDate, DateTime ToDate, string server)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Statistic_SumaryByCommunity", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@ServerName", server);

                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ServiceID"></param>
        /// <param name="FromDate"></param>
        /// <param name="ToDate"></param>
        /// <returns></returns>
        public static DataTable Statistic_OAuthPayment(string ServiceID, DateTime FromDate, DateTime ToDate)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Statistic_OAuthPayment", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@FromDate", FromDate);
                    cmd.Parameters.AddWithValue("@ToDate", ToDate);

                    return DbHelper.FillDataTable(cmd, "Statistic");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion


        #region Bill nạp tiền trực tiếp

        /// <summary>
        /// Danh sách hóa đơn nạp tiền chưa được duyệt
        /// </summary>
        /// <returns></returns>
        public static DataTable Bill_SelectNotAccept()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_SelectNotAccept", connection);
                    return DbHelper.FillDataTable(cmd, Meta.BILL);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy thông tin của hóa đơn (chưa được duyệt) để xử lý
        /// </summary>
        /// <param name="BillID"></param>
        /// <returns></returns>
        public static DataRow Bill_DetailsForAccept(long BillID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_DetailsForAccept", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@BillID", SqlDbType.BigInt, BillID));
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Tạo hóa đơn nạp tiền
        /// </summary>
        /// <param name="LoginName"></param>
        /// <param name="Account"></param>
        /// <param name="GameServer"></param>
        /// <param name="Amount">Số tiền thu của khách hàng</param>
        /// <param name="CardLogAmount">Số tiền thực nạp vào game (sau khi đã cộng thêm %)</param>
        public static void Bill_Create(string LoginName, string Account, string GameID, string GameServer, int Amount, int CardLogAmount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_Create", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Account", SqlDbType.NVarChar, Account));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameID", SqlDbType.NVarChar, GameID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@GameServer", SqlDbType.NVarChar, GameServer));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Amount", SqlDbType.Int, Amount));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@CardLogAmount", SqlDbType.Int, CardLogAmount));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Xóa hóa đơn nạp tiền
        /// </summary>
        /// <param name="LoginName"></param>
        /// <param name="BillID"></param>
        public static void Bill_Delete(string LoginName, long BillID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_Delete", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@BillID", SqlDbType.BigInt, BillID));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Duyệt chấp nhận hóa đơn nạp tiền
        /// </summary>
        /// <param name="LoginName"></param>
        /// <param name="BillID"></param>
        /// <param name="IncRate"></param>
        /// <param name="IPAddress"></param>
        public static void Bill_Accept(string LoginName, long BillID, string IPAddress)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Bill_Accept", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@LoginName", SqlDbType.NVarChar, LoginName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@BillID", SqlDbType.BigInt, BillID));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@IPAddress", SqlDbType.NVarChar, IPAddress));

                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Nạp tiền trực tiếp vào ví tiền
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="RechargeUser"></param>
        /// <param name="Amount"></param>
        /// <param name="IP"></param>
        /// <param name="Msg"></param>
        /// <returns></returns>
        public static bool Wallet_AddDirectly(string UserName, string RechargeUser, int Amount, string IP, string Msg, string Type)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmdInit = new SqlCommand("sp_Wallet_RechargeInit", connection);
                    cmdInit.CommandType = CommandType.StoredProcedure;
                    cmdInit.Parameters.AddWithValue("@UserName", UserName);
                    cmdInit.Parameters.AddWithValue("@RechargeUser", RechargeUser);
                    cmdInit.Parameters.AddWithValue("@Type", Type);
                    cmdInit.Parameters.AddWithValue("@Serial", "");
                    cmdInit.Parameters.AddWithValue("@PinNumber", "");
                    cmdInit.Parameters.AddWithValue("@IP", IP);

                    long rechargeID = Utils.Converter.ToLong(cmdInit.ExecuteScalar(), -1);

                    if (rechargeID <= 0)
                    {
                        return false;
                    }
                    else
                    {
                        SqlCommand cmdFinish = new SqlCommand("sp_Wallet_RechargeFinish", connection);
                        cmdFinish.CommandType = CommandType.StoredProcedure;
                        cmdFinish.Parameters.AddWithValue("@RechargeID", rechargeID);
                        cmdFinish.Parameters.AddWithValue("@Amount", Amount);
                        cmdFinish.Parameters.AddWithValue("@Status", 1);
                        cmdFinish.Parameters.AddWithValue("@ErrorCode", 0);
                        cmdFinish.Parameters.AddWithValue("@Msg", Msg);
                        cmdFinish.Parameters.AddWithValue("@Voucher", "");
                        SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                        pResult.Direction = ParameterDirection.Output;
                        cmdFinish.Parameters.Add(pResult);

                        cmdFinish.ExecuteNonQuery();

                        int result = Utils.Converter.ToInt(pResult.Value);
                        if (result <= 0)
                        {
                            return false;
                        }
                        else
                        {
                            return true;
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion


        #region Khác

        /// <summary>
        /// Danh sách Game
        /// </summary>
        /// <returns></returns>
        public static DataTable Game_Select()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_Game_Select", connection);
                    return DbHelper.FillDataTable(cmd, "Game");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Danh sách game được cấp quyền quản trị cho user
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static DataTable Game_SelectForAdmin(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Game_SelectForAdmin", connection);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    return DbHelper.FillDataTable(cmd, "Game");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable Server_Select(bool addAllRow)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_GameServer_Select", connection);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    DataTable dt = DbHelper.FillDataTable(cmd, Meta.SERVER);
                    if (addAllRow)
                    {
                        DataRow dr = dt.NewRow();
                        dr[Lib.Meta.SERVER_NAME] = "";
                        dr[Lib.Meta.SERVER_FULLNAME] = "---- Tất cả --";
                        dt.Rows.InsertAt(dr, 0);
                    }
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetActiveCode(int page, int pagesize, string pincode, string serial, string type, string kind, int turn, int status, string account, string usedaccount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetActiveCode", connection);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@Page", page);
                    cmd.Parameters.AddWithValue("@PageSize", pagesize);
                    cmd.Parameters.AddWithValue("@PinCode", pincode);
                    cmd.Parameters.AddWithValue("@Serial", serial);
                    cmd.Parameters.AddWithValue("@Type", type);
                    cmd.Parameters.AddWithValue("@Kind", kind);
                    cmd.Parameters.AddWithValue("@Turn", turn);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.Parameters.AddWithValue("@Account", account);
                    cmd.Parameters.AddWithValue("@UsedAccount", usedaccount);
                    DataTable dt = DbHelper.FillDataTable(cmd, Meta.SERVER);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetVoucherCode(int page, int pagesize, string pincode, string rate, int status, string usedaccount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetVoucherCode", connection);
                    cmd.Parameters.AddWithValue("@Page", page);
                    cmd.Parameters.AddWithValue("@PageSize", pagesize);
                    cmd.Parameters.AddWithValue("@PinCode", pincode);
                    cmd.Parameters.AddWithValue("@Rate", rate);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.Parameters.AddWithValue("@UsedAccount", usedaccount);
                    DataTable dt = DbHelper.FillDataTable(cmd, Meta.SERVER);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataTable GetActiveCodeBySerial(string serial, string account)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetActiveCodeBySerial", connection);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@Serial", serial);
                    cmd.Parameters.AddWithValue("@Account", account);
                    DataTable dt = DbHelper.FillDataTable(cmd, Meta.SERVER);
                    return dt;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int GetTotalActiveCode(string pincode, string serial, string type, string kind, int turn, int status, string account, string usedaccount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetTotalActiveCode", connection);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@PinCode", pincode);
                    cmd.Parameters.AddWithValue("@Serial", serial);
                    cmd.Parameters.AddWithValue("@Type", type);
                    cmd.Parameters.AddWithValue("@Kind", kind);
                    cmd.Parameters.AddWithValue("@Turn", turn);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.Parameters.AddWithValue("@Account", account);
                    cmd.Parameters.AddWithValue("@UsedAccount", usedaccount);
                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                    pResult.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(pResult);
                    DbHelper.ExecuteNonQuery(cmd);
                    return Lib.Utils.Converter.ToInt(pResult.Value, -99);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int GetTotalVoucherCode(string pincode, string rate, int status, string usedaccount)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_GetTotalVoucherCode", connection);
                    cmd.Parameters.AddWithValue("@PinCode", pincode);
                    cmd.Parameters.AddWithValue("@Rate", rate);
                    cmd.Parameters.AddWithValue("@Status", status);
                    cmd.Parameters.AddWithValue("@UsedAccount", usedaccount);
                    SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                    pResult.Direction = ParameterDirection.Output;
                    cmd.Parameters.Add(pResult);
                    DbHelper.ExecuteNonQuery(cmd);
                    return Lib.Utils.Converter.ToInt(pResult.Value, -99);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static DataRow Server_Details(int ServerID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("sp_GameServer_DetailsByID", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServerID", ServerID);

                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch
            {
                return null;
            }
        }

        public static void Server_Insert(int ServerID, string ServerName, string FullName, string ServerIdentityName, int SortOrder)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GameServer_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServerID", ServerID);
                    cmd.Parameters.AddWithValue("@ServerName", ServerName);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@FullName", FullName);
                    cmd.Parameters.AddWithValue("@ServerIdentityName", ServerIdentityName);
                    cmd.Parameters.AddWithValue("@SortOrder", SortOrder);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static void Server_Update(int ServerID, string ServerName, string FullName, string ServerIdentityName,
                                         int SortOrder, int ServerStatus, int IsHot,
                                         string DNS, string GoldTransferLink, string IDCheckLink, string GetCharacterLink, string LoginLink)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GameServer_Update", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServerID", ServerID);
                    cmd.Parameters.AddWithValue("@ServerName", ServerName);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    cmd.Parameters.AddWithValue("@FullName", FullName);
                    cmd.Parameters.AddWithValue("@ServerIdentityName", ServerIdentityName);
                    cmd.Parameters.AddWithValue("@SortOrder", SortOrder);
                    cmd.Parameters.AddWithValue("@ServerStatus", ServerStatus);
                    cmd.Parameters.AddWithValue("@IsHot", IsHot);
                    cmd.Parameters.AddWithValue("@DNS", DNS);
                    cmd.Parameters.AddWithValue("@GoldTransferLink", GoldTransferLink);
                    cmd.Parameters.AddWithValue("@IDCheckLink", IDCheckLink);
                    cmd.Parameters.AddWithValue("@GetCharacterLink", GetCharacterLink);
                    cmd.Parameters.AddWithValue("@LoginLink", LoginLink);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        public static void Server_UpdateStatus(int ServerID, string GameID, int Status)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GameServer_UpdateStatus", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServerID", ServerID);
                    cmd.Parameters.AddWithValue("@GameID", GameID);
                    cmd.Parameters.AddWithValue("@ServerStatus", Status);

                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Danh sách tỉnh/thành phố
        /// </summary>
        /// <returns></returns>
        public static DataTable Location_Select()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_Location_Select", connection);
                    return DbHelper.FillDataTable(cmd, Meta.LOCATION);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Danh sách câu hỏi bí mật
        /// </summary>
        /// <returns></returns>
        public static DataTable Question_Select()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("sp_Question_Select", connection);
                    return DbHelper.FillDataTable(cmd, Meta.LOCATION);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Kiểm ra quyền của user trên một chức năng xem user có được cấp quyền hay không
        /// </summary>
        /// <param name="FunctionName"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static bool Function_CheckRight(string FunctionName, string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Function_CheckRight", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FunctionName", SqlDbType.NVarChar, FunctionName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    return Utils.Converter.ToBoolean(DbHelper.ExecuteScalar(cmd));
                }
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// Lấy danh sách các chức năng và quyền được cấp cho user trên mỗi chức năng
        /// </summary>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public static DataTable Function_SelectUserRight(string UserName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Function_SelectUserRight", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    return DbHelper.FillDataTable(cmd, "AdminFunction_User");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cấp/hủy quyền cho user trên chức năng của hệ thống
        /// </summary>
        /// <param name="FunctionName"></param>
        /// <param name="UserName"></param>
        /// <param name="Allow"></param>
        public static void Function_SetUserRight(string FunctionName, string UserName, bool Allow)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_Function_SetUserRight", connection);
                    cmd.Parameters.Add(DbHelper.CreateParamter("@FunctionName", SqlDbType.NVarChar, FunctionName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@UserName", SqlDbType.NVarChar, UserName));
                    cmd.Parameters.Add(DbHelper.CreateParamter("@Allow", SqlDbType.Bit, Allow));
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy danh sách các User đã được cấp quyền quản trị hệ thống
        /// </summary>
        /// <returns></returns>
        public static DataTable Function_SelectGrantedUser()
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_Function_SelectGrantedUser", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    return DbHelper.FillDataTable(cmd, "GrantedUser");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Xem log giao dịch của User
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable SelectAdminLog(string UserName, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_SelectAdminLog", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "AdminLog");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Ghi log thao tác của User
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="IPAddress"></param>
        /// <param name="LogAction"></param>
        public static void WriteLog(string UserName, string IPAddress, string LogAction)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_WriteLog", connection);
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@IPAddress", IPAddress);
                    cmd.Parameters.AddWithValue("@LogAction", LogAction);
                    cmd.ExecuteNonQuery();
                }
            }
            catch // (Exception ex)
            {
                //throw ex;
            }
        }

        public static string Write_CACK_EventLog(string UserName, string ServerName, string AdditionalData)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("sp_CACK_EventLog_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@EventName", "EVENT000");
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@servername", ServerName);
                    cmd.Parameters.AddWithValue("@AdditionalData", AdditionalData);
                    cmd.Parameters.AddWithValue("@IsFinish", 1);
                    cmd.ExecuteNonQuery();
                    return "1";
                }
            }
            catch (Exception ex)
            {
                return "0";
            }
        }

        public static void LockUnlockAccount(string Username, int Status, string Note)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_LockUnlockAccount", connection);
                    cmd.Parameters.AddWithValue("@Username", Username);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    cmd.Parameters.AddWithValue("@Note", Note);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {

            }
        }

        public static DataTable GetLockUnlockAccount(string Username)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_GetLockUnlockAccount", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", Username);
                    return DbHelper.FillDataTable(cmd, "LockUnlockAccount");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Chanelling

        /// <summary>
        /// Lấy danh sách đối tác chanelling theo game
        /// </summary>
        /// <param name="gameID"></param>
        /// <returns></returns>
        public static DataTable ChanellingPartner_SelectByGame(string gameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingPartner_SelectByGame", connection);
                    cmd.Parameters.AddWithValue("@GameID", gameID);
                    return DbHelper.FillDataTable(cmd, "ChanellingPartner");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy danh sách server game được cấp cho đối tác chanelling
        /// </summary>
        /// <param name="partnerID"></param>
        /// <returns></returns>
        public static DataTable ChanellingGameServer_SelectByPartner(string partnerID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_SelectByPartner", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    return DbHelper.FillDataTable(cmd, "ChanellingGameServer");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thông tin chi tiết server
        /// </summary>
        /// <param name="partnerID"></param>
        /// <param name="serverName"></param>
        /// <returns></returns>
        public static DataRow ChanellingGameServer_Details(string partnerID, string serverName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_Details", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@ServerName", serverName);
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="partnerID"></param>
        /// <param name="serverName"></param>
        /// <param name="playEnabled"></param>
        /// <param name="topupEnabled"></param>
        /// <param name="notes"></param>
        public static void ChanellingGameServer_ChangeStatus(string partnerID, string serverName, int playEnabled, int topupEnabled, string notes)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_ChangeStatus", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@ServerName", serverName);
                    cmd.Parameters.AddWithValue("@PlayEnabled", playEnabled);
                    cmd.Parameters.AddWithValue("@TopupEnabled", topupEnabled);
                    cmd.Parameters.AddWithValue("@Notes", notes);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="partnerID"></param>
        /// <param name="serverName"></param>
        /// <param name="gameID"></param>
        public static void ChanellingGameServer_Delete(string partnerID, string serverName, string gameID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_Delete", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@ServerName", serverName);
                    cmd.Parameters.AddWithValue("@GameID", gameID);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int ChanellingGameServer_Add(string partnerID, string serverName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_ChanellingGameServer_Add", connection);
                    cmd.Parameters.AddWithValue("@PartnerID", partnerID);
                    cmd.Parameters.AddWithValue("@ServerName", serverName);
                    cmd.Parameters.AddWithValue("@GameID", AppManager.GameID);
                    int rowsAffected = cmd.ExecuteNonQuery();
                    return rowsAffected;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        #endregion

        #region OAuth - Payment Service

        /// <summary>
        /// Danh sách các OAuthApplication
        /// </summary>
        /// <param name="SearchValue"></param>
        /// <param name="Page"></param>
        /// <param name="PageSize"></param>
        /// <returns></returns>
        public static DataTable OAuthApplication_Select(string SearchValue, int Page, int PageSize)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@SearchValue", SearchValue);
                    cmd.Parameters.AddWithValue("@Page", Page);
                    cmd.Parameters.AddWithValue("@PageSize", PageSize);

                    return DbHelper.FillDataTable(cmd, "OAuthApplication");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Thông tin chi tiết Application
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static DataRow OAuthApplication_Details(string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);

                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Application đã tồn tại?
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static bool OAuthApplication_Exists(string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Exists", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);

                    return Convert.ToBoolean(DbHelper.ExecuteScalar(cmd));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Bổ sung ứng dụng
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="ClientID"></param>
        /// <param name="Secret"></param>
        /// <param name="ApplicationName"></param>
        /// <param name="Site"></param>
        /// <param name="Logo"></param>
        /// <param name="Enabled"></param>
        /// <param name="AlwaysTrust"></param>
        /// <returns></returns>
        public static bool OAuthApplication_Insert(string UserName, string ClientID, string Secret, string ApplicationName, string Site, string Logo, bool Enabled, bool AlwaysTrust, string CssLink, string PopupCssLink, string JsLink, string RegSourceID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    cmd.Parameters.AddWithValue("@Secret", Secret);
                    cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
                    cmd.Parameters.AddWithValue("@Site", Site);
                    cmd.Parameters.AddWithValue("@Logo", Logo);
                    cmd.Parameters.AddWithValue("@Enabled", Enabled);
                    cmd.Parameters.AddWithValue("@AlwaysTrust", AlwaysTrust);
                    cmd.Parameters.AddWithValue("@CssLink", CssLink);
                    cmd.Parameters.AddWithValue("@PopupCssLink", PopupCssLink);
                    cmd.Parameters.AddWithValue("@JsLink", JsLink);
                    cmd.Parameters.AddWithValue("@RegSourceID", RegSourceID);

                    int rowsAffected = DbHelper.ExecuteNonQuery(cmd);
                    return rowsAffected != 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật thông tin ứng dụng
        /// </summary>
        /// <param name="UserName"></param>
        /// <param name="ClientID"></param>
        /// <param name="Secret"></param>
        /// <param name="ApplicationName"></param>
        /// <param name="Site"></param>
        /// <param name="Logo"></param>
        /// <param name="Enabled"></param>
        /// <param name="AlwaysTrust"></param>
        /// <returns></returns>
        public static bool OAuthApplication_Update(string UserName, string ClientID, string Secret, string ApplicationName, string Site, string Logo, bool Enabled, bool AlwaysTrust, string CssLink, string PopupCssLink, string JsLink, string RegSourceID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_Update", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@UserName", UserName);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    cmd.Parameters.AddWithValue("@Secret", Secret);
                    cmd.Parameters.AddWithValue("@ApplicationName", ApplicationName);
                    cmd.Parameters.AddWithValue("@Site", Site);
                    cmd.Parameters.AddWithValue("@Logo", Logo);
                    cmd.Parameters.AddWithValue("@Enabled", Enabled);
                    cmd.Parameters.AddWithValue("@AlwaysTrust", AlwaysTrust);
                    cmd.Parameters.AddWithValue("@CssLink", CssLink);
                    cmd.Parameters.AddWithValue("@PopupCssLink", PopupCssLink);
                    cmd.Parameters.AddWithValue("@JsLink", JsLink);
                    cmd.Parameters.AddWithValue("@RegSourceID", RegSourceID);

                    int rowsAffected = DbHelper.ExecuteNonQuery(cmd);
                    return rowsAffected != 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Danh sách RedirectURI của Application
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static DataTable OAuthApplication_SelectRedirectURI(string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_SelectRedirectURI", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    return DbHelper.FillDataTable(cmd, "OAuthApplication_RedirectURI");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Cập nhật RedirectURI
        /// </summary>
        /// <param name="ID"></param>
        /// <param name="RedirectURI"></param>
        public static void OAuthApplication_UpdateRedirectURIs(string ClientID, string[] RedirectURIs)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthApplication_DeleteRedirectURIs", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    DbHelper.ExecuteNonQuery(cmd);

                    using (SqlCommand cmd1 = new SqlCommand("spAdmin_OAuthApplication_AddRedirectURI", connection))
                    {
                        cmd1.CommandType = CommandType.StoredProcedure;
                        SqlParameter pClientID = new SqlParameter("@ClientID", ClientID);
                        SqlParameter pRedirectURI = new SqlParameter("@RedirectURI", SqlDbType.NVarChar, 500);
                        cmd1.Parameters.Add(pClientID);
                        cmd1.Parameters.Add(pRedirectURI);
                        foreach (string uri in RedirectURIs)
                        {
                            if (!string.IsNullOrEmpty(uri.Trim()))
                            {
                                pRedirectURI.Value = uri.Trim();
                                cmd1.ExecuteNonQuery();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static DataTable OAuthPaymentService_Select(string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Select", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    return DbHelper.FillDataTable(cmd, "OAuthPayment_Service");
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static bool OAuthPaymentService_Exists(string ServiceID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Exists", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);

                    return Convert.ToBoolean(DbHelper.ExecuteScalar(cmd));
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ServiceID"></param>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static DataRow OAuthPaymentService_Details(string ServiceID, string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Details", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    return DbHelper.FillDataRow(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ServiceID"></param>
        /// <param name="ServiceName"></param>
        /// <param name="ServiceKey"></param>
        /// <param name="ServiceDesc"></param>
        /// <param name="GosuTransferType"></param>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static bool OAuthPaymentService_Insert(string ServiceID, string ServiceName, string ServiceKey, string ServiceDesc, string GosuTransferType, string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@ServiceName", ServiceName);
                    cmd.Parameters.AddWithValue("@ServiceKey", ServiceKey);
                    cmd.Parameters.AddWithValue("@ServiceDesc", ServiceDesc);
                    cmd.Parameters.AddWithValue("@GosuTransferType", GosuTransferType);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    int rowsAffected = DbHelper.ExecuteNonQuery(cmd);
                    return rowsAffected != 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="ServiceID"></param>
        /// <param name="ServiceName"></param>
        /// <param name="ServiceKey"></param>
        /// <param name="ServiceDesc"></param>
        /// <param name="GosuTransferType"></param>
        /// <param name="ClientID"></param>
        /// <returns></returns>
        public static bool OAuthPaymentService_Update(string ServiceID, string ServiceName, string ServiceKey, string ServiceDesc, string GosuTransferType, string ClientID)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_OAuthPaymentService_Update", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@ServiceID", ServiceID);
                    cmd.Parameters.AddWithValue("@ServiceName", ServiceName);
                    cmd.Parameters.AddWithValue("@ServiceKey", ServiceKey);
                    cmd.Parameters.AddWithValue("@ServiceDesc", ServiceDesc);
                    cmd.Parameters.AddWithValue("@GosuTransferType", GosuTransferType);
                    cmd.Parameters.AddWithValue("@ClientID", ClientID);
                    int rowsAffected = DbHelper.ExecuteNonQuery(cmd);
                    return rowsAffected != 0;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }



        #endregion

        #region Admin Commands

        /// <summary>
        /// Thay đổi trạng thái của hình thức thanh toán
        /// </summary>
        /// <param name="Type"></param>
        /// <param name="Status"></param>
        public static void PaymentType_Update(string Type, int Status)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = DbHelper.CreateCommand("spAdmin_PaymentType_Update", connection);
                    cmd.Parameters.AddWithValue("@Type", Type);
                    cmd.Parameters.AddWithValue("@Status", Status);
                    DbHelper.ExecuteNonQuery(cmd);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion


        ///server list 
        static List<string> gameServerList = new List<string>() { "vn03", "vn04" };
        static WebDB()
        {
            gameServerList = WebDB_N.ListServer().Select(s => s.tablezone).ToList();
        }
        ///key space
        static string KeySpaceCas = "gh_stats";


        /// <summary>
        /// Lấy dữ liệu ccu
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<Ccu> getCcu(List<DateTime> dateTimeList)
        {
            List<Ccu> result = new List<Ccu>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    DateTime fromDt = dateTimeList[0];
                    DateTime toDt = dateTimeList[dateTimeList.Count - 1];
                    Double totime = DataToolUtil.kindDateTimeToKindUnixTimestamp(toDt, DateTimeKind.Local);
                    Double startime = DataToolUtil.kindDateTimeToKindUnixTimestamp(fromDt, DateTimeKind.Local);

                    // ConverterWorkData.truncateTime(ccuItem.Timestamp, "dd/MM/yyyy HH:mm");

                    SqlCommand cmd = new SqlCommand("SELECT timestamp,gamezone,zonename ,onlinemun FROM ccu where [timestamp] between " + startime + " and " + totime + " order by [timestamp] desc", connection);
                    try
                    {
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            Ccu ccu = new Ccu();
                            ccu.Timestamp = dataReader["timestamp"].ToString();
                            ccu.GameZone = dataReader["gamezone"].ToString();
                            ccu.ZoneName = dataReader["zonename"].ToString();
                            ccu.Num = dataReader["onlinemun"].ToString();
                            result.Add(ccu);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    {
                    }

                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        ///get cuu to sql server
        ///






        /// <summary>
        /// Insert dữ liệu ccu
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static void insertCcu(List<Ccu> ccuList)
        {
            foreach (var item in ccuList)
            {
                try
                {
                    using (SqlConnection connection = ConnectionHelper.GetConnectionForWds())
                    {
                        SqlCommand cmd = DbHelper.CreateCommand("spWds_InsertCcu", connection);
                        cmd.Parameters.AddWithValue("@Timestamp", item.Timestamp);
                        cmd.Parameters.AddWithValue("@GameZone", item.GameZone);
                        cmd.Parameters.AddWithValue("@Num", item.Num);
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {

                }
            }
        }
        /// <summary>
        /// Lấy dữ liệu au
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<Au> getAu(List<DateTime> dateTimeList)
        {
            List<Au> result = new List<Au>();
            List<string> gameZoneList = new List<string>() { "1004", "1003", "1002" };
            double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
            double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(DateTime.ParseExact(dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59", "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture), DateTimeKind.Local);
            try
            {
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    string cmdText = "select timestamp,gamezone,account,ip from au where timestamp>={0} and timestamp <={1} AND gamezone='{2}' ALLOW FILTERING;";

                    foreach (string zone in gameZoneList)
                    {
                        cmdText = string.Format(cmdText, fromTS, toTS, zone);
                        RowSet rowSet = session.Execute(cmdText);
                        foreach (Row row in rowSet.GetRows())
                        {
                            Au au = new Au();
                            au.Timestamp = row.GetValue<long>("timestamp").ToString();
                            au.GameZone = row.GetValue<string>("gamezone").ToString();
                            au.Account = row.GetValue<string>("account").ToString();
                            au.IP = row.GetValue<string>("ip").ToString();
                            string timeStamp = DataToolUtil.truncateTime(au.Timestamp, "dd/MM/yyyy");
                            au.Timestamp = timeStamp;
                            au.Date = DataToolUtil.kindUnixTimeStampToKindDateTime(double.Parse(timeStamp), DateTimeKind.Local, DateTimeKind.Local).ToString("dd/MM/yyyy");
                            au.DateTime = DataToolUtil.kindUnixTimeStampToKindDateTime(long.Parse(timeStamp), DateTimeKind.Local, DateTimeKind.Local);
                            result.Add(au);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


        public static List<Au> InsertAUDataToSqlserver(List<DateTime> datetime)
        {
            List<Au> result = new List<Au>();
            try
            {

                string query = "";
                string Cql = "select account,gamezone,ip, timestamp from gh_stats.au;";

                query = Cql;// String.Format(Cql, gameServer);
                ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
                PreparedStatement state = localSession.Prepare(query);
                BoundStatement boundStatement = new BoundStatement(state);
                RowSet r = localSession.Execute(boundStatement);
                if (r != null)
                {
                    using (SqlConnection conection = ConnectionHelper.GetConnection())
                    {
                        foreach (Row row in r.GetRows())
                        {
                            Au au = new Au();
                            au.Timestamp = row.GetValue<long>("timestamp").ToString();
                            au.GameZone = row.GetValue<string>("gamezone").ToString();
                            au.Account = row.GetValue<string>("account").ToString();
                            au.IP = row.GetValue<string>("ip").ToString();
                            result.Add(au);
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// Thêm dữ liệu thống kê vô bảng ScoredCardSave
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static void insertScoredCardSave()
        {
            try
            {
                int scoredCardSaveNum = 0;
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    try
                    {
                        string cmdText = "select point from pointbonus";
                        RowSet rowSet = session.Execute(cmdText);
                        foreach (Row r in rowSet)
                        {
                            scoredCardSaveNum += int.Parse(r["point"].ToString());
                        }
                    }
                    catch (Exception ex)
                    {

                    }
                }

                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string now = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");
                    SqlCommand cmd = new SqlCommand("sp_Insert_ScoredCardSave", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@NowTime", now);
                    cmd.Parameters.AddWithValue("@PointSave", scoredCardSaveNum);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Thêm dữ liệu thống kê vô bảng goldsave
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static void insertGoldSave()
        {
            try
            {
                int goldSaveNum = 0;

                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (var server in gameServerList)
                    {
                        try
                        {
                            string cmdText = "select cb_gold from statisticaldata" + server;
                            RowSet rowSet = session.Execute(cmdText);

                            foreach (Row r in rowSet)
                            {
                                goldSaveNum += int.Parse(r["cb_gold"].ToString());
                            }
                        }
                        catch (Exception ex)
                        {

                        }
                    }

                }

                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    string now = DateTime.Now.AddDays(-1).ToString("yyyy-MM-dd HH:mm:ss");
                    SqlCommand cmd = new SqlCommand("sp_Insert_GoldSave", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@NowTime", now);
                    cmd.Parameters.AddWithValue("@GoldSave", goldSaveNum);
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// Lấy dữ liệu mua vật phẩm ở gold shop
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        /// 
        public static List<OriginGoldShopData> getItemBuyingData(List<DateTime> dateTimeList, string shopName)
        {

            List<OriginGoldShopData> result = new List<OriginGoldShopData>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {

                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = connection;
                        if (shopName.Equals("All") == false)
                        {
                            cmd.CommandText = "SELECT objname,baseid,dstid,changes,tmpinfo,bind_or_not,datetime FROM goldshop" + gameServer + " WHERE action = 'buy_npc' and " + "tmpinfo=N'" + shopName + "' and type=1" + " and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59'";
                        }
                        else
                        {
                            //cmd.CommandText = "SELECT objname,baseid,dstid,changes,tmpInfo,bind_or_not FROM GOLDSHOP" + gameServer + dt.ToString("yyyyMMdd") + " WHERE action = 'buy_npc' and srcid=0 and num=0 and objid=0 and (type=0 or type=1)";
                            cmd.CommandText = "SELECT objname,baseid,dstid,changes,tmpinfo,bind_or_not,datetime FROM goldshop" + gameServer + " WHERE action = 'buy_npc' and type=1" + " and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59'"; ;
                        }
                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.ItemName = dataReader.GetString(0);
                                itPu.BaseId = dataReader.GetString(1);
                                itPu.CharacterId = dataReader.GetString(2);
                                itPu.PurchasingNum = dataReader.GetInt32(3).ToString();
                                itPu.TmpInfo = dataReader.GetString(4);
                                itPu.Bing_Or_Not = dataReader.GetString(5);
                                itPu.DateStr = dataReader.GetDateTime(6).ToString("dd/MM/yyyy");
                                itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                result.Add(itPu);
                            }
                            dataReader.Close();
                        }
                        catch (Exception ex)
                        { }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// Lấy dữ liệu mua vật phẩm ở gold shop
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        /// 
        public static List<OriginGoldShopData> getItemPurchasingData(List<DateTime> dateTimeList, string shopName)
        {

            List<OriginGoldShopData> result = new List<OriginGoldShopData>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {

                        SqlCommand cmd = new SqlCommand();
                        cmd.Connection = connection;
                        if (shopName.Equals("All") == false)
                        {
                            cmd.CommandText = "select tmpInfo,baseid,objname,  sum(changes) as mun,COUNT(distinct(dstname)) as mun_buy, pitem.pricegolditem as price,CAST(datetime AS date) AS 'day' from goldshop" + gameServer + " join pitem on pitem.iditem = baseid where type=1 and action='buy_npc' and " + "tmpInfo=N'" + shopName + "' and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59' GROUP BY baseid, objname,pricegolditem ,CAST(datetime AS date),tmpInfo order by CAST(datetime AS date) desc,tmpInfo";
                            // cmd.CommandText = "SELECT objname,baseid,dstid,changes,tmpInfo,bind_or_not FROM goldshop" + gameServer + " WHERE action like 'buy_npc' and " + "tmpInfo=N'" + shopName + "' and type=1";
                        }
                        else
                        {
                            cmd.CommandText = "select tmpInfo,baseid,objname,  sum(changes) as mun,COUNT(distinct(dstname)) as mun_buy, pitem.pricegolditem as price,CAST(datetime AS date) AS 'day' from goldshop" + gameServer + " join pitem on pitem.iditem = baseid where type=1 and action='buy_npc' and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59' GROUP BY baseid, objname,pricegolditem ,CAST(datetime AS date),tmpInfo order by CAST(datetime AS date) desc,tmpInfo";
                        }
                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.CharacterId = dataReader["dstid"].ToString();
                                itPu.ItemName = dataReader["objname"].ToString();
                                itPu.BaseId = dataReader["baseid"].ToString();
                                itPu.TmpInfo = dataReader["tmpInfo"].ToString();

                                itPu.priceGold = dataReader["price"].ToString();
                                itPu.MunUserby = dataReader["mun_buy"].ToString();
                                itPu.TotalGoldSpend = dataReader["mun"].ToString();
                                itPu.DateTime = Convert.ToDateTime(dataReader["day"].ToString());
                                result.Add(itPu);
                            }
                            dataReader.Close();
                        }
                        catch (Exception ex)
                        { }

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// Lấy dữ liệu tiêu tốn vật phẩm ở gold shop
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<OriginGoldShopData> getCommonConsumingData(List<DateTime> dateTimeList)
        {

            List<OriginGoldShopData> result = new List<OriginGoldShopData>();

            try
            {
                using (MySql.Data.MySqlClient.MySqlConnection connection = ConnectionHelper.GetConnectionForGhStats())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        foreach (DateTime dt in dateTimeList)
                        {
                            MySqlCommand cmd = new MySqlCommand("SELECT objname,baseid,dstid,changes,tmpInfo,bind_or_not FROM GOLDSHOP" + gameServer + dt.ToString("yyyyMMdd") + " WHERE tmpInfo = '-' and srcid=0 and objid=0 and (type=0 or type=1) and (bind_or_not = 'b' or bind_or_not = 'n')", connection);
                            try
                            {
                                MySqlDataReader dataReader = cmd.ExecuteReader();
                                while (dataReader.Read())
                                {
                                    OriginGoldShopData itPu = new OriginGoldShopData();
                                    itPu.ItemName = dataReader.GetString("objname");
                                    itPu.BaseId = dataReader.GetString("baseid");
                                    itPu.CharacterId = dataReader.GetString("dstid");
                                    itPu.PurchasingNum = dataReader.GetString("changes");
                                    itPu.TmpInfo = dataReader.GetString("tmpInfo");
                                    itPu.Bing_Or_Not = dataReader.GetString("bind_or_not");
                                    itPu.DateStr = dt.ToString("dd/MM/yyyy");
                                    itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    result.Add(itPu);
                                }
                                dataReader.Close();
                            }
                            catch (Exception ex)
                            { }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// Lấy dữ liệu vàng đổi từ phiếu điểm trong bảng gold shop
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<OriginGoldShopData> getGoldExchangeFromScoredCardData(List<DateTime> dateTimeList)
        {

            List<OriginGoldShopData> result = new List<OriginGoldShopData>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        SqlCommand cmd = new SqlCommand("SELECT changes,datetime from goldshop" + gameServer + " WHERE objname = 'Vàng' and srcid =0 and baseid=65534 and tmpInfo= '-' and type=1 and (action=N'Nhấp Thẻ Đổi Vàng' or action = N'㿨ҽ')" + " and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59'", connection);
                        cmd.CommandType = CommandType.Text;
                        try
                        {
                            cmd.CommandTimeout = 30000;
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                //itPu.ItemName = dataReader.GetString("objname");
                                //itPu.CharacterId = dataReader.GetString("dstid");
                                itPu.PurchasingNum = dataReader.GetInt32(0).ToString();
                                //itPu.TmpInfo = dataReader.GetString("tmpInfo");
                                // itPu.Bing_Or_Not = dataReader.GetString("bind_or_not");
                                itPu.DateStr = dataReader.GetDateTime(1).ToString("dd/MM/yyyy");
                                itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                result.Add(itPu);
                            }
                            dataReader.Close();
                        }
                        catch (Exception ex)
                        { }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// Lấy dữ liệu tiêu hao vàng trong bảng gold shop
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<OriginGoldShopData> getUsingGoldData(List<DateTime> dateTimeList)
        {

            List<OriginGoldShopData> result = new List<OriginGoldShopData>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {

                        SqlCommand cmd = new SqlCommand("SELECT changes,datetime FROM goldshop" + gameServer + " WHERE objname = 'Vàng' and baseid=65534 and type=0 " + " and datetime between '" + dateTimeList[0].ToString("yyyy-MM-dd") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd") + " 23:59:59'", connection);
                        try
                        {
                            cmd.CommandTimeout = 30000;
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.PurchasingNum = dataReader.GetInt32(0).ToString();
                                //itPu.DateStr = dt.ToString("dd/MM/yyyy");
                                //ItPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                itPu.DateStr = dataReader.GetDateTime(1).ToString("dd/MM/yyyy");
                                itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                result.Add(itPu);
                            }
                            dataReader.Close();
                        }
                        catch (Exception ex)
                        { }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// Lấy dữ liệu vàng tồn kho
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<GoldSave> getGoldSaveData(List<DateTime> dateTimeList)
        {

            List<GoldSave> result = new List<GoldSave>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("SELECT datetime,goldsave FROM GoldSave" + " WHERE datetime between " + "'" + dateTimeList[0].ToString("yyyy-MM-dd ") + "00:00:00'" + " and " + "'" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + "23:59:59'", connection);
                    try
                    {
                        cmd.CommandTimeout = 30000;
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            GoldSave gs = new GoldSave();
                            gs.GoldS = dataReader.GetString(1);
                            gs.DateStr = dataReader.GetDateTime(0).ToString("dd/MM/yyyy");
                            gs.Date = DateTime.ParseExact(gs.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                            result.Add(gs);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// Lấy dữ liệu phiếu điểm tồn kho
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<ScoredCardSave> getScoredCardSaveData(List<DateTime> dateTimeList)
        {

            List<ScoredCardSave> result = new List<ScoredCardSave>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("SELECT datetime,pointsave FROM ScoredCardSave" + " WHERE datetime between " + "'" + dateTimeList[0].ToString("yyyy-MM-dd ") + "00:00:00'" + " and " + "'" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + "23:59:59'", connection);
                    try
                    {
                        cmd.CommandTimeout = 30000;
                        SqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            ScoredCardSave scs = new ScoredCardSave();
                            scs.PointSave = dataReader.GetString(1);
                            scs.DateStr = dataReader.GetDateTime(0).ToString("dd/MM/yyyy");
                            scs.Date = DateTime.ParseExact(scs.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                            result.Add(scs);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    { }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// Lấy dữ liệu bảng gold shop
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<OriginGoldShopData> getGoldShopData(List<DateTime> dateTimeList)
        {

            List<OriginGoldShopData> result = new List<OriginGoldShopData>();

            try
            {
                using (MySql.Data.MySqlClient.MySqlConnection connection = ConnectionHelper.GetConnectionForGhStats())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        foreach (DateTime dt in dateTimeList)
                        {
                            MySqlCommand cmd = new MySqlCommand("SELECT objname,objid,dstid,changes,tmpInfo,bind_or_not,baseid,type,srcid,action FROM GOLDSHOP" + gameServer + dt.ToString("yyyyMMdd"), connection);
                            try
                            {
                                MySqlDataReader dataReader = cmd.ExecuteReader();
                                while (dataReader.Read())
                                {
                                    OriginGoldShopData itPu = new OriginGoldShopData();
                                    itPu.ItemName = dataReader.GetString("objname");
                                    itPu.ItemId = dataReader.GetString("objid");
                                    itPu.CharacterId = dataReader.GetString("dstid");
                                    itPu.PurchasingNum = dataReader.GetString("changes");
                                    itPu.TmpInfo = dataReader.GetString("tmpInfo");
                                    itPu.Bing_Or_Not = dataReader.GetString("bind_or_not");
                                    itPu.BaseId = dataReader.GetString("baseid");
                                    itPu.Type = dataReader.GetString("type");
                                    itPu.ScrId = dataReader.GetString("srcid");
                                    itPu.Action = dataReader.GetString("action");
                                    itPu.DateStr = dt.ToString("dd/MM/yyyy");
                                    itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    result.Add(itPu);
                                }
                                dataReader.Close();
                            }
                            catch (Exception ex)
                            { }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// Lấy level of character in charbase 
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static Dictionary<string, List<CharBase>> getCharBaseData(List<DateTime> dateTimeList)
        {

            Dictionary<string, List<CharBase>> result = new Dictionary<string, List<CharBase>>();
            double fromTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(dateTimeList[0], DateTimeKind.Local);
            double toTS = DataToolUtil.kindDateTimeToKindUnixTimestamp(dateTimeList[dateTimeList.Count - 1], DateTimeKind.Local);
            List<int> typeList = new List<int> { 1, 2, 3, 4, 5, 6 };
            try
            {
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        List<CharBase> list = new List<CharBase>();
                        foreach (int type in typeList)
                        {
                            string cmdText = "SELECT level,createtime,charid FROM " + "charbase" + gameServer + " WHERE " + " createtime >= " + fromTS + " and createtime <=" + toTS + " and type=" + type + " ALLOW FILTERING";
                            try
                            {
                                RowSet rowSet = session.Execute(cmdText);
                                foreach (Row r in rowSet)
                                {
                                    CharBase cb = new CharBase();
                                    cb.Level = r["level"].ToString();
                                    cb.CharId = r["charid"].ToString();
                                    cb.DateStr = DataToolUtil.kindUnixTimeStampToKindDateTime(double.Parse(r["createtime"].ToString()), DateTimeKind.Local, DateTimeKind.Local).ToString("dd/MM/yyyy");
                                    cb.DateTime = DateTime.ParseExact(cb.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    list.Add(cb);
                                }
                            }
                            catch (Exception ex)
                            { }
                        }

                        result.Add(gameServer, list.Where(w => int.Parse(w.CharId) > 1000).ToList());
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// Đếm số nhân vật được tạo mới
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>

        public static Dictionary<string, List<CharBase>> getStatsCreateInCharBase()
        {
            Dictionary<string, List<CharBase>> result = new Dictionary<string, List<CharBase>>();
            try
            {
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        List<CharBase> list = new List<CharBase>();

                        string cmdText = "SELECT charid  FROM " + "charbase" + gameServer;
                        try
                        {
                            RowSet rowSet = session.Execute(cmdText);
                            foreach (Row r in rowSet)
                            {
                                CharBase cb = new CharBase();
                                cb.CharId = r["charid"].ToString();
                                //cb.totalCreateCharacter = r["total"].ToString();
                                list.Add(cb);
                            }
                        }
                        catch (Exception ex)
                        { }
                        List<CharBase> listNew = new List<CharBase>();
                        CharBase cbNEW = new CharBase();
                        cbNEW.totalCreateCharacter = list.Where(w => int.Parse(w.CharId) > 1000).Count().ToString();
                        listNew.Add(cbNEW);
                        result.Add(gameServer, listNew);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


        /// <summary>
        /// Get số lượng chọn phái
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>

        public static Dictionary<string, List<CharBase>> getStatsChoiceFaceInCharBase()
        {
            Dictionary<string, List<CharBase>> result = new Dictionary<string, List<CharBase>>();
            try
            {
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        List<CharBase> list = new List<CharBase>();

                        //string cmdText="SELECT CAREER,COUNT(CHARID) as total FROM " + "CHARBASE" + gameServer + " WHERE CHARID > 1000 GROUP BY CAREER";
                        string cmdText = "SELECT career,charid FROM " + "charbase" + gameServer;
                        try
                        {
                            RowSet rowSet = session.Execute(cmdText);
                            foreach (Row r in rowSet)
                            {
                                CharBase cb = new CharBase();
                                cb.face = r["career"].ToString();
                                cb.CharId = r["charid"].ToString();
                                //cb.totalChoiceFace = dataReader.GetString("total");
                                list.Add(cb);
                            }
                        }
                        catch (Exception ex)
                        { }
                        List<CharBase> listnew = (from tb in list where int.Parse(tb.CharId) > 1000 group tb by new { tb.face } into grp select new CharBase { face = grp.Key.face, totalChoiceFace = grp.Count().ToString() }).ToList();
                        result.Add(gameServer, listnew);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }





        /// <summary>
        /// Lấy dữ liệu consume
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<Consume> getConsumeLogData(List<DateTime> dateTimeList)
        {
            List<Consume> result = new List<Consume>();

            try
            {
                using (MySql.Data.MySqlClient.MySqlConnection connection = ConnectionHelper.GetConnectionForGhStats())
                {

                    MySqlCommand cmd = new MySqlCommand("SELECT OPERTIME, CONSUMEPOINT FROM CONSUMELOG WHERE OPERTIME between " + "'" + dateTimeList[0].ToString("yyyy-MM-dd ") + "00:00:00'" + " and " + "'" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + "23:59:59'", connection);
                    try
                    {
                        MySqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            Consume cs = new Consume();
                            cs.CONSUMEPOINT = dataReader.GetString("CONSUMEPOINT");
                            cs.OPERTIMESTR = dataReader.GetDateTime("OPERTIME").ToString("dd/MM/yyyy");
                            cs.OPERTIME = DateTime.ParseExact(cs.OPERTIMESTR, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                            result.Add(cs);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    {
                    }
                    //}
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


        public static List<pCard> getPCardData(List<DateTime> dateTimeList)
        {
            List<pCard> result = new List<pCard>();
            try
            {
                using (MySql.Data.MySqlClient.MySqlConnection connection = ConnectionHelper.GetConnectionForGhStats())
                {
                    MySqlCommand cmd = new MySqlCommand("SELECT actid,gakey,useuid,DATE(usetime) as usetime FROM pcard WHERE usetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' ORDER BY DATE(usetime) desc , actid asc", connection);
                    try
                    {
                        MySqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            pCard pC = new pCard();
                            // pC.actid = !dataReader.IsDBNull(0) ? dataReader.GetString("actid") : "0";
                            pC.useuid = !dataReader.IsDBNull(2) ? dataReader.GetString("useuid") : "0";
                            pC.gakey = !dataReader.IsDBNull(1) ? dataReader.GetString("gakey") : "0";
                            //    pC.usetime = !dataReader.IsDBNull(3) ? dataReader.GetDateTime("usetime") : DateTime.Now;
                            result.Add(pC);
                        }
                        dataReader.Close();
                    }
                    catch (Exception ex)
                    {

                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// Lấy dữ liệu consume
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static List<FilledCard> getFilledCardData(List<DateTime> dateTimeList)
        {
            List<FilledCard> result = new List<FilledCard>();
            List<int> typeList = new List<int> { 0 };
            try
            {
                using (ISession session = ConnectionHelper.getConnectionForCasGhStats())
                {
                    foreach (int type in typeList)
                    {
                        string cmdText = "SELECT filltime,account,point,orderid,uid,source,state,tid,type FROM filledcard WHERE type=" + type + " and filltime >= " + "'" + dateTimeList[0].ToString("yyyy-MM-dd ") + "00:00:00'" + " and filltime <= " + "'" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + "23:59:59' ALLOW FILTERING";
                        try
                        {
                            RowSet rowSet = session.Execute(cmdText);
                            //List<Row> rList=rowSet.GetRows().ToList();
                            foreach (Row r in rowSet)
                            {
                                FilledCard fc = new FilledCard();
                                fc.FILLTIMESTR = r.GetValue<DateTime>("filltime").ToString("dd/MM/yyyy");
                                fc.FILLTIME = DateTime.ParseExact(fc.FILLTIMESTR, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                fc.ORIGIONFILLTIME = r.GetValue<DateTime>("filltime").ToLocalTime().AddHours(7);
                                fc.POINT = r["point"].ToString();
                                fc.ACCOUNT = r["account"].ToString();
                                fc.ORDERID = r["orderid"].ToString();
                                fc.UID = r["uid"].ToString();
                                fc.SOURCE = r["source"].ToString();
                                fc.STATE = r["state"].ToString();
                                fc.TID = r["tid"].ToString();
                                fc.TYPE = r["type"].ToString();
                                result.Add(fc);
                            }
                        }
                        catch (Exception ex)
                        {
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
        /// <summary>
        /// Lấy già vàng của item
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>
        public static string getGoldPriceOfItem(string baseid)
        {
            string price = "";
            try
            {
                using (MySql.Data.MySqlClient.MySqlConnection connection = ConnectionHelper.GetConnectionForGhStats())
                {
                    MySqlCommand cmd = new MySqlCommand("SELECT pricegolditem FROM pitem where iditem=" + baseid, connection);
                    try
                    {
                        MySqlDataReader dataReader = cmd.ExecuteReader();
                        while (dataReader.Read())
                        {
                            price = dataReader.GetString("pricegolditem");
                            dataReader.Close();
                            return price;
                        }

                    }
                    catch (Exception ex)
                    {
                        return price;
                    }
                }
            }
            catch (Exception ex)
            {
                return price;
                throw ex;
            }
            return price;
        }

        public static string write_ErrorLog(string key, string msg)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("sp_ErrorLog_Insert", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Key", "APIGH");
                    cmd.Parameters.AddWithValue("@Msg", msg);
                    cmd.ExecuteNonQuery();
                    return "1";
                }
            }
            catch (Exception ex)
            {
                return "0";
            }
        }


        public static Dictionary<string, List<OriginGoldShopData>> getChangePointGoldshop(List<DateTime> dateTimeList)
        {
            Dictionary<string, List<OriginGoldShopData>> result = new Dictionary<string, List<OriginGoldShopData>>();
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                        List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();

                        SqlCommand cmd = new SqlCommand("SELECT SUM(changes) as totalpoint, CAST(datetime AS date) AS 'day' FROM goldshop" + gameServer + " WHERE objname=N'Vàng' and srcid=0 and baseid=65534 and tmpInfo='-' and type=1 and (action=N'Nhấp Thẻ Đổi Vàng' or action = N'㿨ҽ') and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' GROUP BY CAST(datetime AS date)", connection);
                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.TotalGoldPoint = !dataReader.IsDBNull(0) ? dataReader["totalpoint"].ToString() : "0";
                                string day = dataReader["day"].ToString();
                                itPu.DateTime = !dataReader.IsDBNull(1) ? Convert.ToDateTime(day) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                r.Add(itPu);
                            }
                            dataReader.Close();
                            int i = 0;
                            foreach (DateTime d in dateTimeList)
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                if (r.Count > i && r[i].DateTime == d)
                                {
                                    itPu = r[i];
                                    i++;
                                }
                                else
                                {
                                    itPu.TotalGoldPoint = "0";
                                    itPu.DateTime = d;

                                }
                                rlast.Add(itPu);
                            }

                        }
                        catch (Exception ex)
                        { }

                        result.Add(gameServer, rlast);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }


        public static Dictionary<string, List<OriginGoldShopData>> getTotalGoldSpend(List<DateTime> dateTimeList)
        {
            Dictionary<string, List<OriginGoldShopData>> result = new Dictionary<string, List<OriginGoldShopData>>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                        List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();

                        SqlCommand cmd = new SqlCommand("SELECT SUM(changes) AS totalpoint,CAST(datetime AS date) AS 'day' FROM goldshop" + gameServer + " where objname=N'Vàng' and type=0 and baseid=65534 and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' GROUP BY CAST(datetime AS date)", connection);

                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.TotalGoldSpend = !dataReader.IsDBNull(0) ? dataReader["totalpoint"].ToString() : "0";
                                string day = dataReader["day"].ToString();
                                itPu.DateTime = !dataReader.IsDBNull(1) ? Convert.ToDateTime(day) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                r.Add(itPu);
                            }
                            dataReader.Close();
                            int i = 0;
                            foreach (DateTime d in dateTimeList)
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                if (r.Count > i && r[i].DateTime == d)
                                {
                                    itPu = r[i];
                                    i++;
                                }
                                else
                                {
                                    itPu.TotalGoldPoint = "0";
                                    itPu.DateTime = d;

                                }
                                rlast.Add(itPu);
                            }
                        }
                        catch (Exception ex)
                        { }

                        result.Add(gameServer, rlast);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static Dictionary<string, List<OriginGoldShopData>> getTotalGoldSpendSilver(List<DateTime> dateTimeList)
        {
            Dictionary<string, List<OriginGoldShopData>> result = new Dictionary<string, List<OriginGoldShopData>>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                        List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();
                        SqlCommand cmd = new SqlCommand("SELECT SUM(changes) AS totalpoint,CAST(datetime AS date) AS 'day' FROM goldshop" + gameServer + " where objname=N'Vàng' and type=0 and dstid=0 and dstname='(null)' and action=N'Giao dịch đấu giá vàng' and baseid=65534 and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' GROUP BY CAST(datetime AS date)", connection);

                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.TotalGoldSpend = !dataReader.IsDBNull(0) ? dataReader["totalpoint"].ToString() : "0";
                                string day = dataReader["day"].ToString();
                                itPu.DateTime = !dataReader.IsDBNull(1) ? Convert.ToDateTime(day) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                r.Add(itPu);
                            }
                            dataReader.Close();
                            int i = 0;
                            foreach (DateTime d in dateTimeList)
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                if (r.Count > i && r[i].DateTime == d)
                                {
                                    itPu = r[i];
                                    i++;
                                }
                                else
                                {
                                    itPu.TotalGoldPoint = "0";
                                    itPu.DateTime = d;

                                }
                                rlast.Add(itPu);
                            }
                        }
                        catch (Exception ex)
                        { }
                        result.Add(gameServer, rlast);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static Dictionary<string, List<OriginGoldShopData>> getTotalGoldChangeSilver(List<DateTime> dateTimeList)
        {
            Dictionary<string, List<OriginGoldShopData>> result = new Dictionary<string, List<OriginGoldShopData>>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();
                        List<OriginGoldShopData> rlast = new List<OriginGoldShopData>();
                        SqlCommand cmd = new SqlCommand("SELECT SUM(changes) AS totalpoint,CAST(datetime AS date) AS 'day' FROM goldshop" + gameServer + " where objname='Vàng' and type=0 and dstid=0 and dstname='(null)' and action=N'Vàng đổi Bạc' and baseid=65534 and datetime BETWEEN '" + dateTimeList[0].ToString("yyyy-MM-dd ") + " 00:00:00' AND '" + dateTimeList[dateTimeList.Count - 1].ToString("yyyy-MM-dd ") + " 23:59:59' GROUP BY CAST(datetime AS date)", connection);

                        try
                        {
                            SqlDataReader dataReader = cmd.ExecuteReader();
                            while (dataReader.Read())
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                itPu.TotalGoldSpend = !dataReader.IsDBNull(0) ? dataReader["totalpoint"].ToString() : "0";
                                string day = dataReader["day"].ToString();
                                itPu.DateTime = !dataReader.IsDBNull(1) ? Convert.ToDateTime(day) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                r.Add(itPu);
                            }
                            dataReader.Close();
                            int i = 0;
                            foreach (DateTime d in dateTimeList)
                            {
                                OriginGoldShopData itPu = new OriginGoldShopData();
                                if (r.Count > i && r[i].DateTime == d)
                                {
                                    itPu = r[i];
                                    i++;
                                }
                                else
                                {
                                    itPu.TotalGoldPoint = "0";
                                    itPu.DateTime = d;

                                }
                                rlast.Add(itPu);
                            }
                        }
                        catch (Exception ex)
                        { }

                        result.Add(gameServer, rlast);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }



        public static Dictionary<string, List<OriginGoldShopData>> getTotalGoldIBShop(List<DateTime> dateTimeList)
        {
            Dictionary<string, List<OriginGoldShopData>> result = new Dictionary<string, List<OriginGoldShopData>>();

            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    foreach (string gameServer in gameServerList)
                    {
                        List<OriginGoldShopData> r = new List<OriginGoldShopData>();

                        foreach (DateTime dt in dateTimeList)
                        {

                            SqlCommand cmd = new SqlCommand("select baseid,objname,  sum(changes) as mun,pitem.pricegolditem as price,CAST(datetime AS date) AS 'day' from goldshop" + gameServer + " join pitem on pitem.iditem = baseid where type=1 and action='buy_npc' and tmpInfo=N'Tiệm Tùy Thân' and objname NOT IN (N'Gia Tộc Trợ Lệnh',N'Gia Tộc Lệnh',N'Lăng Ba Phù',N'Khinh Công Phù',N'Hồi Thành Quyển') and CAST(datetime AS date)='" + dt.ToString("yyyyMMdd") + "' GROUP BY baseid, objname,pricegolditem ,CAST(datetime AS date)", connection);

                            try
                            {
                                SqlDataReader dataReader = cmd.ExecuteReader();
                                int total = 0;
                                while (dataReader.Read())
                                {
                                    string mun = !dataReader.IsDBNull(0) ? dataReader["mun"].ToString() : "0";
                                    string priceGold = !dataReader.IsDBNull(0) ? dataReader["price"].ToString() : "0";
                                    total += Convert.ToInt32(mun) * Convert.ToInt32(priceGold);
                                }
                                if (!dataReader.Read())
                                {
                                    OriginGoldShopData itPu = new OriginGoldShopData();
                                    itPu.TotalGoldSpend = total.ToString();
                                    itPu.DateStr = dt.ToString("dd/MM/yyyy");
                                    itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    // itPu.DateTime = !dataReader.IsDBNull(1) ? DateTime.ParseExact(dataReader["day"].ToString(), "dd/MM/yyyy", CultureInfo.InvariantCulture) : DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    r.Add(itPu);
                                }
                                else
                                {
                                    OriginGoldShopData itPu = new OriginGoldShopData();
                                    itPu.TotalGoldSpend = "0";
                                    itPu.DateStr = dt.ToString("dd/MM/yyyy");
                                    itPu.DateTime = DateTime.ParseExact(itPu.DateStr, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                                    r.Add(itPu);
                                }
                                dataReader.Close();
                            }
                            catch (Exception ex)
                            { }
                        }
                        result.Add(gameServer, r);
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        /// <summary>
        /// Get goldshop databy 
        /// </summary>
        /// <param name="StartDate"></param>
        /// <param name="EndDate"></param>
        /// <returns>List<Ccu></returns>

        public static bool InsertGoldShopDataToSqlserver(string day)
        {
            try
            {
                string query = "";
                string Cql = "select createid,objid,baseid,objname,tmpinfo,num,changes,type,srcid,dstname,dstid,action,datetime,bind_or_not from gh_stats.goldshop{0} where key =true and datetime>='{1} 00:00:00' and datetime <= '{1} 23:59:59' ALLOW FILTERING;"; //  objname = 'Vàng' and baseid = '65534' 
                foreach (string gameServer in gameServerList)
                {
                    query = String.Format(Cql, gameServer, day);
                    ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
                    PreparedStatement state = localSession.Prepare(query);
                    BoundStatement boundStatement = new BoundStatement(state);
                    RowSet r = localSession.Execute(boundStatement);
                    if (r != null)
                    {
                        using (SqlConnection conection = ConnectionHelper.GetConnection())
                        {
                            foreach (Row row in r.GetRows())
                            {
                                string b = row.GetValue<string>("bind_or_not");

                                SqlCommand cmd = new SqlCommand("spTransfer_Insert_Goldshop", conection);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@createid", row.GetValue<string>("createid"));
                                cmd.Parameters.AddWithValue("@objid", row.GetValue<string>("objid"));
                                cmd.Parameters.AddWithValue("@objname", row.GetValue<string>("objname"));
                                cmd.Parameters.AddWithValue("@type", row.GetValue<string>("type"));
                                cmd.Parameters.AddWithValue("@num", row.GetValue<string>("num"));
                                cmd.Parameters.AddWithValue("@changes", row.GetValue<string>("changes"));
                                cmd.Parameters.AddWithValue("@srcid", row.GetValue<string>("srcid"));
                                cmd.Parameters.AddWithValue("@tmpInfo", row.GetValue<string>("tmpinfo"));
                                cmd.Parameters.AddWithValue("@dstid", row.GetValue<string>("dstid"));
                                cmd.Parameters.AddWithValue("@dstname", row.GetValue<string>("dstname"));
                                cmd.Parameters.AddWithValue("@baseid", row.GetValue<string>("baseid"));
                                cmd.Parameters.AddWithValue("@action", row.GetValue<string>("action"));
                                cmd.Parameters.AddWithValue("@datetime", row.GetValue<DateTime>("datetime"));
                                cmd.Parameters.AddWithValue("@bind_or_not", b == "" ? "(null)" : b);
                                cmd.Parameters.AddWithValue("@tablename", "goldshop" + gameServer);
                                SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                pResult.Direction = ParameterDirection.Output;
                                cmd.Parameters.Add(pResult);
                                cmd.ExecuteNonQuery();
                                // return Convert.ToInt32(pResult.Value) == 1;   
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        public static bool InsertCharbaseDataToSqlserver()
        {
            try
            {
                string query = "";
                string Cql = "select charid,accid,name, name , level , face ,createip,lastactivedate,onlinetime ,offlinetime, gold, gold_score , createtime from gh_stats.charbasevn{0}";
                foreach (string gameServer in gameServerList)
                {
                    query = String.Format(Cql, gameServer);
                    ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
                    PreparedStatement state = localSession.Prepare(query);
                    BoundStatement boundStatement = new BoundStatement(state);
                    RowSet r = localSession.Execute(boundStatement);
                    if (r != null)
                    {
                        using (SqlConnection conection = ConnectionHelper.GetConnection())
                        {
                            foreach (Row row in r.GetRows())
                            {
                                SqlCommand cmd = new SqlCommand("spTransfer_Insert_Charbase", conection);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@charid", row.GetValue<string>("charid"));
                                cmd.Parameters.AddWithValue("@accid", row.GetValue<string>("accid"));
                                cmd.Parameters.AddWithValue("@name", row.GetValue<string>("name"));
                                cmd.Parameters.AddWithValue("@name", row.GetValue<string>("name"));
                                cmd.Parameters.AddWithValue("@level", row.GetValue<string>("level"));
                                cmd.Parameters.AddWithValue("@face", row.GetValue<string>("face"));
                                cmd.Parameters.AddWithValue("@createip", row.GetValue<string>("createip"));
                                cmd.Parameters.AddWithValue("@lastactivedate", row.GetValue<string>("lastactivedate"));
                                cmd.Parameters.AddWithValue("@onlinetime", row.GetValue<string>("onlinetime"));
                                cmd.Parameters.AddWithValue("@offlinetime", row.GetValue<string>("offlinetime"));
                                cmd.Parameters.AddWithValue("@gold", row.GetValue<string>("gold"));
                                cmd.Parameters.AddWithValue("@golds_core", row.GetValue<string>("golds_core"));
                                cmd.Parameters.AddWithValue("@createtime", row.GetValue<DateTime>("createtime"));
                                SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                pResult.Direction = ParameterDirection.Output;
                                cmd.Parameters.Add(pResult);
                                cmd.ExecuteNonQuery();
                                // return Convert.ToInt32(pResult.Value) == 1;    jrururururruur
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }


        //SELECT actid,DATE(usetime),count(actid) as usetime FROM pcard  GROUP  BY DATE(usetime) desc , actid asc

        public static bool InsertPcardToSqlserver(string day)
        {
            try
            {
                string query = "";
                string Cql = "SELECT actid,gakey,useuid,CAST(datetime AS date) AS usetime FROM pcard WHERE usetime BETWEEN '{0} 00:00:00' AND '{0} 23:59:59' ORDER BY CAST(datetime AS date) , actid asc";

                query = String.Format(Cql, day);
                ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
                PreparedStatement state = localSession.Prepare(query);
                BoundStatement boundStatement = new BoundStatement(state);
                RowSet r = localSession.Execute(boundStatement);

                if (r != null)
                {
                    List<pCard> resultPcard = new List<pCard>();
                    foreach (Row row in r.GetRows())
                    {
                        pCard pC = new pCard();
                        //   pC.actid = row.GetValue<string>("actid");
                        pC.useuid = row.GetValue<string>("useuid");
                        pC.gakey = row.GetValue<string>("gakey");
                        // pC.usetime = row.GetValue<DateTime>("usetime");
                        resultPcard.Add(pC);
                    }

                    var queryDatapCard = (from pcard in resultPcard
                                          group pcard by new { pcard.usetime, pcard.actid } into g
                                          select new
                                          {
                                              day = g.Select(p => p.usetime.ToString()),
                                              typeid = g.Select(p => p.actid),
                                              countcode = g.Count()
                                          }).ToList();

                    if (queryDatapCard != null)
                    {
                        using (SqlConnection conection = ConnectionHelper.GetConnection())
                        {
                            foreach (var row in queryDatapCard)
                            {
                                SqlCommand cmd = new SqlCommand("spTransfer_Insert_Pcard", conection);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@datetime", row.day);
                                cmd.Parameters.AddWithValue("@typeid", row.typeid);
                                cmd.Parameters.AddWithValue("@count", row.countcode);
                                SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                pResult.Direction = ParameterDirection.Output;
                                cmd.Parameters.Add(pResult);
                                cmd.ExecuteNonQuery();
                                // return Convert.ToInt32(pResult.Value) == 1;   
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }


        public static bool InsertCcuCasToSqlserver(string dateTimeList)
        {
            try
            {
                string query = "";
                string Cql = "select * from gh_stats.onlinenum where gamezone=2491372 and rtimestamp >={0} and rtimestamp <={1} ALLOW FILTERING;";

                DateTime dt = Convert.ToDateTime(dateTimeList);
                DateTime a = DateTime.Now;
                long totime = ConvertToTimestamp(a);
                long startime = ConvertToTimestamp(dt);

                query = String.Format(Cql, startime, totime);
                ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
                PreparedStatement state = localSession.Prepare(query);
                BoundStatement boundStatement = new BoundStatement(state);
                RowSet r = localSession.Execute(boundStatement);


                if (r != null)
                {
                    List<Ccu> result = new List<Ccu>();
                    foreach (Row row in r.GetRows())
                    {
                        Ccu ccu = new Ccu();
                        ccu.GameZone = row.GetValue<int>("gamezone").ToString();
                        ccu.Num = row.GetValue<int>("onlinenum").ToString();
                        ccu.ZoneName = row.GetValue<string>("zonename");
                        ccu.Timestamp = row.GetValue<long>("rtimestamp").ToString();
                        result.Add(ccu);
                    }

                    var queryDataCcu = (from ccu in result
                                        orderby ccu.Timestamp ascending
                                        select ccu);

                    using (SqlConnection conection = ConnectionHelper.GetConnection())
                    {
                        SqlCommand cmdd = new SqlCommand("spTransfer_delete_CCU", conection);
                        cmdd.Parameters.AddWithValue("@timestamp", startime);
                        cmdd.CommandType = CommandType.StoredProcedure;
                        cmdd.ExecuteNonQuery();


                        foreach (var row in queryDataCcu)
                        {
                            DateTime time = UnixTimeStampToDateTime(Convert.ToInt32(row.Timestamp));
                            int m = time.Minute;
                            if (m % 5 == 0)
                            {
                                SqlCommand cmd = new SqlCommand("spTransfer_Insert_CCU", conection);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@onlinemun", row.Num);
                                cmd.Parameters.AddWithValue("@gamezone", row.GameZone);
                                cmd.Parameters.AddWithValue("@zonename", row.ZoneName);
                                cmd.Parameters.AddWithValue("@timestamp", row.Timestamp);
                                SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                pResult.Direction = ParameterDirection.Output;
                                cmd.Parameters.Add(pResult);
                                cmd.ExecuteNonQuery();
                            }

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        public static bool InsertALLCcuCasToSqlserver()
        {

            try
            {
                string query = "";
                string Cql = "select * from gh_stats.onlinenum where gamezone=2491372;";



                query = Cql;//String.Format(Cql, startime, totime);
                ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
                PreparedStatement state = localSession.Prepare(query);
                BoundStatement boundStatement = new BoundStatement(state);
                RowSet r = localSession.Execute(boundStatement);

                if (r != null)
                {
                    List<Ccu> result = new List<Ccu>();
                    foreach (Row row in r.GetRows())
                    {
                        Ccu ccu = new Ccu();
                        ccu.GameZone = row.GetValue<int>("gamezone").ToString();
                        ccu.Num = row.GetValue<int>("onlinenum").ToString();
                        ccu.ZoneName = row.GetValue<string>("zonename");
                        ccu.Timestamp = row.GetValue<long>("rtimestamp").ToString();
                        result.Add(ccu);
                    }

                    var queryDataCcu = (from ccu in result
                                        orderby ccu.Timestamp ascending
                                        select ccu);


                    using (SqlConnection conection = ConnectionHelper.GetConnection())
                    {
                        /*SqlCommand cmdd = new SqlCommand("spTransfer_delete_CCU", conection);
                        cmdd.CommandType = CommandType.StoredProcedure;
                        cmdd.ExecuteNonQuery();*/

                        foreach (var row in queryDataCcu)
                        {
                            DateTime time = UnixTimeStampToDateTime(Convert.ToInt32(row.Timestamp));
                            int m = time.Minute;
                            if (m % 5 == 0)
                            {
                                SqlCommand cmd = new SqlCommand("spTransfer_Insert_CCU", conection);
                                cmd.CommandType = CommandType.StoredProcedure;
                                cmd.Parameters.AddWithValue("@onlinemun", row.Num);
                                cmd.Parameters.AddWithValue("@gamezone", row.GameZone);
                                cmd.Parameters.AddWithValue("@zonename", row.ZoneName);
                                cmd.Parameters.AddWithValue("@timestamp", row.Timestamp);
                                SqlParameter pResult = new SqlParameter("@Result", SqlDbType.Int);
                                pResult.Direction = ParameterDirection.Output;
                                cmd.Parameters.Add(pResult);
                                cmd.ExecuteNonQuery();
                            }

                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        public static DateTime UnixTimeStampToDateTime(double unixTimeStamp)
        {
            // Unix timestamp is seconds past epoch
            System.DateTime dtDateTime = new DateTime(1970, 1, 1, 0, 0, 0, 0, System.DateTimeKind.Utc);
            dtDateTime = dtDateTime.AddSeconds(unixTimeStamp).ToLocalTime();
            return dtDateTime;
        }

        private static long ConvertToTimestamp(DateTime value)
        {
            long epoch = (value.Ticks - 621355968000000000) / 10000000;
            return epoch;
        }

        public static bool updatecasgs()
        {
            try
            {
                string query = "UPDATE gh_stats.goldshopvn03 SET key = 'true' where id = {0} ;";
                string Cql = "select id,createid,objid,baseid,objname,tmpinfo,num,changes,type,srcid,dstname,dstid,action,datetime,bind_or_not from gh_stats.goldshopvn03;"; //  objname = 'Vàng' and baseid = '65534' 


                ISession localSession = ConnectionHelper.getConnectionForCasGhStats();
                PreparedStatement state = localSession.Prepare(Cql);
                BoundStatement boundStatement = new BoundStatement(state);
                RowSet r = localSession.Execute(boundStatement);
                if (r != null)
                {

                    foreach (Row row in r.GetRows())
                    {
                        string a = row.GetValue<Guid>("id").ToString();
                        query = String.Format(query, a);
                        PreparedStatement s = localSession.Prepare(query);
                        BoundStatement b = new BoundStatement(s);
                        localSession.Execute(boundStatement);
                    }


                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            return false;
        }

        public static int checkUniqueUserName(string userName)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_CheckUniqueUserName", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@userName", userName);
                    SqlParameter returnParameter = cmd.Parameters.Add("RetVal", SqlDbType.Int);
                    returnParameter.Direction = ParameterDirection.ReturnValue;
                    cmd.ExecuteNonQuery();
                    return (int)returnParameter.Value;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public static int createAccount(string userName, string name, string passWord, string email)
        {
            try
            {
                using (SqlConnection connection = ConnectionHelper.GetConnection())
                {
                    SqlCommand cmd = new SqlCommand("spAdmin_CreateAccount", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@userName", userName);
                    cmd.Parameters.AddWithValue("@name", name);
                    cmd.Parameters.AddWithValue("@passWord", CryptHelper.EncodeMD5(passWord));
                    cmd.Parameters.AddWithValue("@email", email);
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

    }
}
